#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : 榛果_十月一爬虫.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-09-04 14:50 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

# import pymongo
import time
# import datetime
import pandas as pd
from lxml.etree import HTML
from report_system.spider.spider import Spider
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils import excel_util
from report_system.utils import style_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receivers = "dt@iyoujia.com"
    # receivers = "zhangjiaheng@iyoujia.com"

else:
    receivers = 'dt@iyoujia.com;songweiyun@iyoujia.com;zhanglin@iyoujia.com;zhangjiaheng@iyoujia.com'
    # receivers = "zhangjiaheng@iyoujia.com"

sql = """select 
    concat(l.id,"") 房源id,
    concat(lh.id,"") 房屋id,
     l.title 标题,
    lh.room_num 居室,
    city.short_name 城市,
    ltl25.third_lodge_id 榛果id,
    sc.name 渠道,
    concat(ifnull(lps.price,CASE
                                WHEN  (FIND_IN_SET(DATE_FORMAT('{recdate}','%w'),
                                                  (CASE 
                                                   WHEN lpw.weekend_type=1 THEN '5,6,0' 
                                                   WHEN lpw.weekend_type=2 THEN '5,6' 
                                                   WHEN lpw.weekend_type=3 THEN '6,0' 
                                                   ELSE '999' 
                                                   END))>0 AND lpw.price IS NOT NULL AND lpw.price > 0) 
                                THEN lpw.price
                                ELSE lpb.price
                                END ),"")/100 有家价格{day}
    from youjia.lodge l 
    left join youjia.lod_house lh on lh.lodge_id = l.id
    left  join youjia.city on city.id = lh.city_id
    left join lod_third_lodge ltl3 on ltl3.lodge_id = l.id and ltl3.sales_channel_id = 3
    left join lod_third_lodge ltl25 on ltl25.lodge_id = l.id and ltl25.sales_channel_id = 25
    left join airbnb_upload_lodge aul on aul.lodge_id = l.id
    left join youjia.lod_price_base lpb on l.id =lpb.lodge_id    
    left join youjia.lod_price_week lpw on l.id= lpw.lodge_id  and lpb.sales_channel_id = lpw.sales_channel_id
	left join youjia.lod_price_special lps on l.id= lps.lodge_id and lpb.sales_channel_id = lps.sales_channel_id  
                                               and lps.dt = '{recdate}' and lps.is_delele = 0
    left join youjia_common.sales_channel sc on sc.id = lpb.sales_channel_id
           
    where l.city_id <> 376
    and lh.city_id <> 376
    and lh.state = 100
    and l.state = 0
    and l.state_super = 10
    and lpb.sales_channel_id = 25
    group by l.id,lpb.sales_channel_id
    # limit 10
    """

date_list = ['2019-10-02', '2019-10-03', '2019-10-04', '2019-10-05', '2019-10-06', '2019-10-07']
sql_format = sql.format(recdate='2019-10-01', day="01")
print(sql_format)
result_df = pd.read_sql_query(sql_format, db_util.con_youjia)
# df02 = pd.read_sql_query(sql.format(recdate='2019-10-02'))

for date in date_list:
    day = date.split("-")[-1]
    sql_format = sql.format(recdate=date, day=day)
    df = pd.read_sql_query(sql_format, db_util.con_youjia)
    result_df = pd.merge(result_df, df, how='outer')

# excel_util.pd_to_excel({"SaaS大所": result_df}, "001榛果价格", engine="xlsxwriter")
all_unit_list = list()


class ZhenGuo(Spider):
    def __init__(self):

        Spider.__init__(self, spider_num=30 if not debug else 10)

    def conversion_rmb(self, fen):
        """
        分转换为元
        :param fen:
        :return:
        """
        try:
            return int(float(fen)) / 100
        except ValueError:
            return 0

    def run(self):
        self.req(handler=self.calendar_daily)
        Spider.run(self)

    def calendar_daily(self):
        for idx, row in result_df.iterrows():
            dict_row = dict(row)
            unit_id = dict_row['榛果id']
            log.info("榛果id:{}".format(unit_id))
            if unit_id and unit_id != "" and str(unit_id) != 0:
                self.req(url="https://www.zhenguo.com/housing/{unit_id}/".format(unit_id=unit_id), method='GET',
                         handler=self.calendar_daily_csrf, unit_id=unit_id, row=dict_row)

        # self.calendar_daily_done()

        # print(df)
        # pass

    def calendar_daily_csrf(self, r, unit_id, row):
        if r.status_code == 200:
            log.error('########查找日历的csrf 成功 {unit_id}'.format(unit_id=unit_id))
            csrf_token = HTML(r.text).xpath('string(//meta[@name="csrf-token"]/@content)')
            headers = dict()
            headers["User-Agent"] = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/" \
                                    "537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36"
            headers["x-csrf-token"] = csrf_token
            headers["Content-Type"] = 'application/json'
            headers['Cookie'] = 'zgwww=' + r.cookies['zgwww']
            data = {"productId": str(unit_id)}
            self.req(url="https://www.zhenguo.com/gw/cprod/api/v1/calendar/query", method='POST',
                     handler=self.calendar_daily_h,
                     headers=headers, json=data, unit_id=unit_id, row=row)
        else:
            all_unit_list.append(row)
            log.error('查找日历的csrf出现错误   {unit_id}'.format(unit_id=unit_id))

    def calendar_daily_h(self, r, j, row, unit_id):
        # log.info("处理 {city} 房源 {unit_id} 日历中".format(unit_id=unit_id, city=city['nm']))
        if r.status_code == 200 and j is not None:

            calendar_dict = j
            calendar_data = calendar_dict['data'] if 'data' in calendar_dict else None
            on_sale = calendar_data['onSale'] if calendar_data and 'onSale' in calendar_data else None
            date_infos = calendar_data['dateInfos'] if calendar_data and 'dateInfos' in calendar_data else []
            # daily = dict()
            for date_info in date_infos:
                _date = str(time.strftime("%Y-%m-%d", time.strptime(str(date_info['date']), "%Y%m%d")))

                price = int(self.conversion_rmb(date_info['price'])) if 'price' in date_info else 0
                log.info("date:{},price:{}".format(_date, price))
                if _date == "2019-10-01":
                    row['榛果01'] = price
                if _date == "2019-10-02":
                    row['榛果02'] = price
                if _date == "2019-10-03":
                    row['榛果03'] = price
                if _date == "2019-10-04":
                    row['榛果04'] = price
                if _date == "2019-10-05":
                    row['榛果05'] = price
                if _date == "2019-10-06":
                    row['榛果06'] = price
                if _date == "2019-10-07":
                    row['榛果07'] = price

        else:
            log.info("=============榛果房源 {unit_id} 失败 日历处理失败 ！=============".format(unit_id=unit_id))
        all_unit_list.append(row)

    def calendar_daily_done(self):
        pass

    # def __del__(self):
    #     temp_list = list(all_unit_list)
    #     df2 = df_util.list_to_df(temp_list)

    # print(temp_list)
    # log.info()
    # last_df = pd.DataFrame(temp_list)
    # log.error(e)
    # date_time = datetime.datetime.now()

    # log.info(last_df)
    # df_dict = {'榛果十月一价格监控': last_df}
    # file_name = title = "榛果十月一价格监控"
    # mail_util.distribute_mail(df_dict=df_dict, file_name=file_name, engine="xlsxwriter",
    #                           title=title, receiver=receivers, index=False)


def mail():
    # temp_list = list(all_unit_list)
    path = "/Users/youjia/work/youjia_project/report_system/report_files/榛果十月一价格监控_2019-09-05.xlsx"
    last_df = pd.read_excel(path)
    # last_df = df_util.list_to_df(temp_list)
    last_df = last_df[["城市", "居室", "房屋id", "房源id", "榛果id", "渠道",
                       "有家价格01", "榛果01", "有家价格02", "榛果02", "有家价格03", "榛果03",
                       "有家价格04", "榛果04", "有家价格05", "榛果05", "有家价格06", "榛果06",
                       "有家价格07", "榛果07", "标题"]]

    # '#FFF5EE', '#FFF0F5', '#FFEFDB', '#FFEFD5', '#FFEC8B', '#FFEBCD', '#FFE7BA', '#FFE4E1', '#FFE4C4',
    #  '#FFE4B5', '#FFE1FF', '#FFDEAD', '#FFDAB9', '#FFD700', '#FFD39B', '#FFC1C1', '#FFC125', '#FFC0CB',
    last_df = style_util.compare_variable(last_df, "有家价格01", "榛果01", color="#FFF5EE")
    last_df = style_util.compare_variable(last_df, "有家价格02", "榛果02", color="#FFEFD5")
    last_df = style_util.compare_variable(last_df, "有家价格03", "榛果03", color="#FFEBCD")
    last_df = style_util.compare_variable(last_df, "有家价格04", "榛果04", color="#FFD700")
    last_df = style_util.compare_variable(last_df, "有家价格05", "榛果05", color="#FFC125")
    last_df = style_util.compare_variable(last_df, "有家价格06", "榛果06", color="#FFC0CB")
    last_df = style_util.compare_variable(last_df, "有家价格07", "榛果07", color="#FFE4C4")

    df_dict = {'榛果十月一价格监控': last_df}
    file_name = title = "榛果十月一价格监控"
    mail_util.distribute_mail(df_dict=df_dict, file_name=file_name, engine="xlsxwriter",
                              title=title, receiver=receivers, index=False, is_df=False)


if __name__ == '__main__':
    # zhenguo = ZhenGuo()
    # zhenguo.run()
    # print("123123123123123 ")
    mail()

# a = [{'房源id': '188048408546655182', '房屋id': '188048528805739637', '标题': '【清新易居】河坊街上/定安路地铁300米', '居室': 1, '城市': '杭州',
#       '榛果id': '2531607', '渠道': '榛果', '有家价格一': 698.0, '有家价格02': 698.0, '有家价格03': 698.0, '有家价格04': 698.0, '有家价格05': 698.0,
#       '有家价格06': 698.0, '有家价格07': 268.0},
#      {'房源id': '188048515920837811', '房屋id': '188048515920837763', '标题': '武昌火车站&近黄鹤楼&现代与时尚一居室', '居室': 1, '城市': '武汉',
#       '榛果id': '7226263', '渠道': '榛果', '有家价格一': 699.0, '有家价格02': 699.0, '有家价格03': 699.0, '有家价格04': 699.0, '有家价格05': 699.0,
#       '有家价格06': 699.0, '有家价格07': 699.0, '榛果01': 699, '榛果02': 699, '榛果03': 699, '榛果04': 699, '榛果05': 699, '榛果06': 699,
#       '榛果07': 699},
#      {'房源id': '188048412841622227', '房屋id': '188048528805739635', '标题': '睡在西湖边/步行至南山路河坊街景区/双床', '居室': 1, '城市': '杭州',
#       '榛果id': '2726241', '渠道': '榛果', '有家价格一': 888.0, '有家价格02': 888.0, '有家价格03': 888.0, '有家价格04': 888.0, '有家价格05': 888.0,
#       '有家价格06': 888.0, '有家价格07': 328.0, '榛果01': 888, '榛果02': 888, '榛果03': 888, '榛果04': 888, '榛果05': 888, '榛果06': 888,
#       '榛果07': 328},
#      {'房源id': '188048515920837805', '房屋id': '188048515920837736', '标题': '黄家湖大学城临江大宅北欧田园三居室', '居室': 3, '城市': '武汉',
#       '榛果id': '7384331', '渠道': '榛果', '有家价格一': 1299.0, '有家价格02': 1299.0, '有家价格03': 1299.0, '有家价格04': 1299.0,
#       '有家价格05': 1299.0, '有家价格06': 1299.0, '有家价格07': 1299.0, '榛果01': 1299, '榛果02': 1299, '榛果03': 1299, '榛果04': 1299,
#       '榛果05': 1299, '榛果06': 1299, '榛果07': 1299},
#      {'房源id': '184298317981693675', '房屋id': '184298317981693669', '标题': 'U+【茶白】近理工东郊记忆地铁直达春熙路', '居室': 2, '城市': '成都',
#       '榛果id': '2930171', '渠道': '榛果', '有家价格一': 668.0, '有家价格02': 668.0, '有家价格03': 668.0, '有家价格04': 668.0, '有家价格05': 668.0,
#       '有家价格06': 668.0, '有家价格07': 888.0, '榛果01': 668, '榛果02': 668, '榛果03': 668, '榛果04': 668, '榛果05': 668, '榛果06': 668,
#       '榛果07': 888},
#      {'房源id': '188048408546655057', '房屋id': '188048515920837856', '标题': 'U+拂柳/迪士尼免接/动物园/大三居', '居室': 3, '城市': '上海',
#       '榛果id': '1771560', '渠道': '榛果', '有家价格一': 1388.0, '有家价格02': 1388.0, '有家价格03': 1388.0, '有家价格04': 1388.0,
#       '有家价格05': 1388.0, '有家价格06': 1388.0, '有家价格07': 888.0, '榛果01': 648, '榛果02': 648, '榛果03': 648, '榛果04': 648,
#       '榛果05': 648, '榛果06': 648, '榛果07': 648},
#      {'房源id': '188048399956720189', '房屋id': '188048515920837769', '标题': 'U+解放碑·临地铁口·山城记忆一居室', '居室': 1, '城市': '重庆',
#       '榛果id': '4419942', '渠道': '榛果', '有家价格一': 420.0, '有家价格02': 420.0, '有家价格03': 420.0, '有家价格04': 420.0, '有家价格05': 420.0,
#       '有家价格06': 420.0, '有家价格07': 199.0, '榛果01': 420, '榛果02': 420, '榛果03': 420, '榛果04': 420, '榛果05': 420, '榛果06': 420,
#       '榛果07': 199},
#      {'房源id': '188048417136589472', '房屋id': '188048408546654847', '标题': 'U+【大气中式】近西湖南宋御街电梯房三居', '居室': 3, '城市': '杭州',
#       '榛果id': '2675653', '渠道': '榛果', '有家价格一': 1688.0, '有家价格02': 1688.0, '有家价格03': 1688.0, '有家价格04': 1688.0,
#       '有家价格05': 1688.0, '有家价格06': 1688.0, '有家价格07': 658.0, '榛果01': 1688, '榛果02': 1688, '榛果03': 1688, '榛果04': 1688,
#       '榛果05': 1688, '榛果06': 1688, '榛果07': 658},
#      {'房源id': '188048408546654830', '房屋id': '188048515920837927', '标题': '花田半亩U+锦里·武侯祠·华西医院大3居', '居室': 3, '城市': '成都',
#       '榛果id': '2880762', '渠道': '榛果', '有家价格一': 988.0, '有家价格02': 988.0, '有家价格03': 988.0, '有家价格04': 988.0, '有家价格05': 988.0,
#       '有家价格06': 988.0, '有家价格07': 988.0, '榛果01': 988, '榛果02': 988, '榛果03': 988, '榛果04': 988, '榛果05': 988, '榛果06': 988,
#       '榛果07': 988},
#      {'房源id': '188048515920838072', '房屋id': '188048515920837923', '标题': '美工之家U+锦里·武侯祠·华西医院小2居', '居室': 2, '城市': '成都',
#       '榛果id': '3039553', '渠道': '榛果', '有家价格一': 888.0, '有家价格02': 888.0, '有家价格03': 888.0, '有家价格04': 888.0, '有家价格05': 888.0,
#       '有家价格06': 888.0, '有家价格07': 888.0, '榛果01': 888, '榛果02': 888, '榛果03': 888, '榛果04': 888, '榛果05': 888, '榛果06': 888,
#       '榛果07': 888}]
# x = pd.DataFrame(a)
# from report_system.utils import style_util
#
# x = style_util.compare_variable(x, "有家价格一", "榛果01")
# x = style_util.compare_variable(x, "有家价格02", "榛果02")
# excel_util.pd_to_excel({"x": x}, "x")
# print(x)
